﻿using Lamex.Base;
using Lamex.mSA.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.mSA.Common
{
    public class GetDataTable
    {
        //获取表【SA_CBM_WT_From_SAP】字段DISABLE_FLAG为空的数据
        public static DataTable Get_SA_CBM_WT_From_SAP()
        {
            DataTable dataTable = new DataTable();
            string sql = @"SELECT [MATERIAL_NO]
      ,[MATERIAL_DESC]
      ,[MATERIAL_VOLUME]
      ,[VOLUME_UNIT]
      ,[MATERIAL_WEIGHT]
      ,[WEIGHT_UNIT]  
      ,PRODUCT_DIMENSION
      ,[MES_DECOR]
      ,[PUCHASE_FLAG]
      ,[STOCK_LOCATION]
      ,[CREATED_USER]
      ,[CREATED_TIME]    
  FROM [LamexMESManager].[dbo].[SA_CBM_WT_From_SAP]
  where ISNULL(DISABLE_FLAG,'')=''";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        //获取表【MO_BOM】存在DN表的生产订单数据
        public static DataTable Get_MO_BOM(DataTable dt)
        {
            //得到传过来DN表的生产订单号(去重)
            DataTable dt_MO = Common.DataTableHelper.Distinct(dt, new string[] { "MO_NO" });
            List<string> list = new List<string>();
            for (int i = 0; i < dt_MO.Rows.Count; i++)
            {
                if (!string.IsNullOrEmpty(dt_MO.Rows[i][0].ToString().Trim()))
                    list.Add(dt_MO.Rows[i][0].ToString().Trim());
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendFormat(@" AND Package_Mo_Main  IN ('{0}')", string.Join("','", list.ToArray()));
            DataTable dataTable = new DataTable();
            string sql = @" SELECT DISTINCT Material_No FROM [LamexMESManager].[dbo].[Transfer_MO_BOM] 
 WHERE 1=1 " + stringBuilder.ToString() + "";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        //获取[SA_CBM_WT_Calculated]表结构
        public static DataTable Get_SA_CBM_WT_Calculated()
        {
            DataTable dataTable = new DataTable();
            string sql = @"SELECT [TRANSPORT_NO]
      ,[ORDER_NO]
      ,[LINE_NO]
      ,[SL]
      ,[MATERIAL_NO]
      ,[MATERIAL_DESC]
      ,[MO_NO]
      ,[PRODUCT_LINE]
      ,[PRODUCT_ROUTING]
      ,[SHIPPING_QTY]
      ,[CARTON_QTY]
      ,[VOLUME_LENGTH]
      ,[VOLUME_WIDTH]
      ,[VOLUME_HEIGHT]
      ,[UNIT_VOLUME]
      ,[SHIPPING_VOLUME]
      ,[UNIT_WEIGHT]
      ,[SHIPPING_WEIGHT]
      ,[SAVE_TIME]
      ,[SAVE_USER]
      ,[CAL_TYPE]
      ,ORDER_QTY
      ,MRP_CONTROLLER
  FROM [LamexMESManager].[dbo].[SA_CBM_WT_Calculated]
  WHERE 1!=1";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        //按物料号查询【WH_CBM_WT_Revised】
        public static DataTable Get_WH_CBM_WT_Revised()
        {
            DataTable dataTable = new DataTable();
            string sql = @"SELECT  [MATERIAL_NO]
      ,[ACTUAL_LENGTH]
      ,[ACTUAL_WIDTH]
      ,[ACTUAL_HEIGHT]
      ,[ACTUAL_WEIGHT]
      ,[UPDATE_TIME]
  FROM [LamexMESManager].[dbo].[WH_CBM_WT_Revised] ";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        //按物料号查询[产品尺寸]
        public static DataTable Get_SA_CBM_WT_From_SAP_Size(string MaterialNo)
        {
            DataTable dataTable = new DataTable();
            string sql = @"SELECT 
       [PRODUCT_DIMENSION]
      ,[MATERIAL_DESC]
  FROM [LamexMESManager].[dbo].[SA_CBM_WT_From_SAP] where MATERIAL_NO='" + MaterialNo + "'";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }

        /// <summary>
        /// 修改DataTable列名为SAP对接列名
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="columsOldName"></param>
        /// <param name="columsNewName"></param>
        /// <returns></returns>
        public static DataTable dataTable_NewColums(DataTable dt)
        {
            //dt.Columns[columsOldName].ColumnName = columsNewName;
            //return dt;

            dt.Columns["MATNR"].ColumnName = "MATERIAL_NO";
            dt.Columns["MAKTX"].ColumnName = "MATERIAL_DESC"; 
            dt.Columns["VOLUM"].ColumnName = "MATERIAL_VOLUME";
            dt.Columns["VOLEH"].ColumnName = "VOLUME_UNIT";
            dt.Columns["BRGEW"].ColumnName = "MATERIAL_WEIGHT";
            dt.Columns["GEWEI"].ColumnName = "WEIGHT_UNIT";
            dt.Columns["SIZE"].ColumnName = "PRODUCT_DIMENSION";
            dt.Columns["DECOR"].ColumnName = "MES_DECOR";
            dt.Columns["BESKZ"].ColumnName = "PUCHASE_FLAG";
            dt.Columns["LGORT"].ColumnName = "STOCK_LOCATION";
            dt.Columns["DATETIME"].ColumnName = "CREATED_TIME";
            dt.Columns["USERNAME"].ColumnName = "CREATED_USER";
            return dt;
        }
        //新增列
        public static DataTable dataTable_AddColums(DataTable dt)
        {
            DataColumn dc1 = new DataColumn("UPDATE_TIME", typeof(DateTime));
            dc1.DefaultValue = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
            dt.Columns.Add(dc1);
            DataColumn dc2 = new DataColumn("UPDATE_USER", typeof(string));
            dc2.DefaultValue = Lamex.Common.Loginer.CurrentUser.AccountName;
            dt.Columns.Add(dc2);
            return dt;
        }
        //插入数据到临时表
        public static DataTable Insert_DataTable(DataTable dt, DataTable dt_Temp, DataTable dt_manual)
        {
            //获取两个交集
            DataTable table_common = Common.DataTableHelper.DataTableContrast(dt_manual, dt, "MATERIAL_NO", "MATERIAL_NO");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt_Temp.NewRow();
                dr["TRANSPORT_NO"] = dt.Rows[i]["TRANSPORT_NO"].ToString();
                dr["ORDER_NO"] = dt.Rows[i]["ORDER_NO"].ToString();
                dr["LINE_NO"] = dt.Rows[i]["LINE_NO"].ToString();
                dr["SL"] = dt.Rows[i]["SL"].ToString();
                dr["MATERIAL_NO"] = dt.Rows[i]["MATERIAL_NO"].ToString();
                dr["MATERIAL_DESC"] = dt.Rows[i]["MATERIAL_DESC"].ToString();
                dr["MO_NO"] = dt.Rows[i]["MO_NO"].ToString();
                dr["PRODUCT_LINE"] = dt.Rows[i]["PRODUCT_LINE"].ToString();
                dr["PRODUCT_ROUTING"] = dt.Rows[i]["PRODUCT_ROUTING"].ToString();
                dr["SHIPPING_QTY"] = dt.Rows[i]["SHIPPING_QTY"].ToString().Length > 0 ? dt.Rows[i]["SHIPPING_QTY"].ToString() : (object)DBNull.Value;
                dr["CARTON_QTY"] = dt.Rows[i]["CARTON_QTY"].ToString().Length > 0 ? dt.Rows[i]["CARTON_QTY"].ToString() : (object)DBNull.Value;
                dr["ORDER_QTY"] = dt.Rows[i]["ORDER_QTY"].ToString().Length > 0 ? dt.Rows[i]["ORDER_QTY"].ToString() : (object)DBNull.Value;
                if (table_common.Rows.Count > 0 && table_common.Select("MATERIAL_NO='" + dt.Rows[i]["MATERIAL_NO"].ToString() + "'").Length > 0)
                {
                    DataTable table_Temp = table_common.Select("MATERIAL_NO='" + dt.Rows[i]["MATERIAL_NO"].ToString() + "'", "UPDATE_TIME desc").CopyToDataTable();
                    //数据库表[WH_CBM_WT_Revised]已设定长宽厚不允许为空，不需再判断
                    decimal LENGTH = Convert.ToDecimal(table_Temp.Rows[0]["ACTUAL_LENGTH"].ToString());
                    decimal WIDTH = Convert.ToDecimal(table_Temp.Rows[0]["ACTUAL_WIDTH"].ToString());
                    decimal HEIGHT = Convert.ToDecimal(table_Temp.Rows[0]["ACTUAL_HEIGHT"].ToString());
                    decimal volume = LENGTH * WIDTH * HEIGHT / 1000000000;
                    dr["VOLUME_LENGTH"] = LENGTH;
                    dr["VOLUME_WIDTH"] = WIDTH;
                    dr["VOLUME_HEIGHT"] = HEIGHT;
                    dr["UNIT_VOLUME"] = volume;
                    dr["UNIT_WEIGHT"] = Convert.ToDecimal(table_Temp.Rows[0]["ACTUAL_WEIGHT"].ToString());
                    dr["CAL_TYPE"] = "人工修正";

                }
                else
                {
                    dr["VOLUME_LENGTH"] = (object)DBNull.Value;
                    dr["VOLUME_WIDTH"] = (object)DBNull.Value;
                    dr["VOLUME_HEIGHT"] = (object)DBNull.Value;
                    dr["UNIT_VOLUME"] = (object)DBNull.Value;//dt.Rows[i]["UNIT_VOLUME"].ToString().Length > 0 ? dt.Rows[i]["UNIT_VOLUME"].ToString() : (object)DBNull.Value;
                    dr["UNIT_WEIGHT"] = (object)DBNull.Value;//dt.Rows[i]["UNIT_WEIGHT"].ToString().Length > 0 ? dt.Rows[i]["UNIT_WEIGHT"].ToString() : (object)DBNull.Value;
                    dr["CAL_TYPE"] = "";
                }
                dr["SHIPPING_VOLUME"] = (object)DBNull.Value; //dt.Rows[i]["SHIPPING_VOLUME"].ToString().Length > 0 ? dt.Rows[i]["SHIPPING_VOLUME"].ToString() : (object)DBNull.Value;
                dr["SHIPPING_WEIGHT"] = (object)DBNull.Value;//dt.Rows[i]["SHIPPING_WEIGHT"].ToString().Length > 0 ? dt.Rows[i]["SHIPPING_WEIGHT"].ToString() : (object)DBNull.Value;
                dr["SAVE_TIME"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
                dr["SAVE_USER"] = Lamex.Common.Loginer.CurrentUser.AccountName;
                dr["MRP_CONTROLLER"]= dt.Rows[i]["MRP_CONTROLLER"].ToString();
                dt_Temp.Rows.Add(dr);
            }
            return dt_Temp;
        }

        //OEM产品CBM及重量计算
        public static DataTable Get_Table_OEM(DataTable table_DN_Temp, DataTable table_Material_OEM,DataTable table_Unit_Convert)
        {
            List<string> list = new List<string>();
            for (int i = 0; i < table_Material_OEM.Rows.Count; i++)
            {
                if (!string.IsNullOrEmpty(table_Material_OEM.Rows[i]["MATERIAL_NO"].ToString().Trim()))
                    list.Add(table_Material_OEM.Rows[i]["MATERIAL_NO"].ToString().Trim());
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendFormat(@" AND MATERIAL_NO  IN ('{0}')", string.Join("','", list.ToArray()));
            DataTable dataTable = new DataTable();
            string sql = @" SELECT [MATERIAL_NO]
      ,[MATERIAL_VOLUME]
      ,[VOLUME_UNIT]
      ,[MATERIAL_WEIGHT]
      ,[WEIGHT_UNIT]   
      ,[PRODUCT_DIMENSION]
      ,[CREATED_USER]
      ,[CREATED_TIME]
  FROM [LamexMESManager].[dbo].[SA_CBM_WT_From_SAP]
  where ISNULL(DISABLE_FLAG,'')='' " + stringBuilder.ToString() + "";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            if (dataTable.Rows.Count > 0)
            {
                //更新【临时表DN】物料号相同的记录
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    decimal dmlUNIT_VOLUME = 0, dmlUNIT_WEIGHT = 0;
                    string strVOLUME_UNIT = dataTable.Rows[i]["VOLUME_UNIT"].ToString().Trim().ToUpper();
                    string strWEIGHT_UNIT = dataTable.Rows[i]["WEIGHT_UNIT"].ToString().Trim().ToUpper();
                    DataRow[] unitVolume = table_Unit_Convert.Select("Main_Unit='M3' and Convert_Unit='" + strVOLUME_UNIT + "'");
                    DataRow[] unitWeight = table_Unit_Convert.Select("Main_Unit='KG' and Convert_Unit='" + strWEIGHT_UNIT + "'");
                    if (unitVolume.Length > 0)
                        dmlUNIT_VOLUME = Convert.ToDecimal(dataTable.Rows[i]["MATERIAL_VOLUME"].ToString()) /
                               Convert.ToDecimal(unitVolume.CopyToDataTable().Rows[0]["Convert_Value"].ToString());
                    if (unitWeight.Length > 0)
                        dmlUNIT_WEIGHT = Convert.ToDecimal(dataTable.Rows[i]["MATERIAL_WEIGHT"].ToString()) /
                               Convert.ToDecimal(unitWeight.CopyToDataTable().Rows[0]["Convert_Value"].ToString());
                    string ProductSize = dataTable.Rows[i]["PRODUCT_DIMENSION"].ToString();
                    string strMaterialNo = dataTable.Rows[i]["MATERIAL_NO"].ToString();
                    var sizeList = Calculate.Fuc_CBM.Product_Size_CBM(ProductSize,0,0,0);
                    DataRow[] arrRows = table_DN_Temp.Select("MATERIAL_NO='" + strMaterialNo + "'");
                    foreach (DataRow row in arrRows)
                    {
                        row["UNIT_VOLUME"] = dmlUNIT_VOLUME;
                        row["UNIT_WEIGHT"] = dmlUNIT_WEIGHT;
                        row["VOLUME_LENGTH"] = sizeList.Item1;
                        row["VOLUME_WIDTH"] = sizeList.Item2;
                        row["VOLUME_HEIGHT"] = sizeList.Item3;
                        row["CAL_TYPE"] = "SAP取数";
                        //CBM和重量为0时插入错误日志表
                        if (dmlUNIT_VOLUME == 0 || dmlUNIT_WEIGHT == 0)
                            Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(row["TRANSPORT_NO"].ToString(), row["MO_NO"].ToString(), row["MATERIAL_NO"].ToString(), row["MATERIAL_DESC"].ToString(),
                                "", "", sizeList.Item1.ToString(), sizeList.Item2.ToString(), sizeList.Item3.ToString(), "SAP取数体积、重量为0或单位转换错误");

                    }

                }

            }
            return table_DN_Temp;
        }

        //配件CBM和重量计算
        public static DataTable Get_Table_PJ(DataTable table_DN_Temp,DataTable table_Material_PJ, DataTable table_Unit_Convert, List<CartonMaterial> cartons)
        {
            DataTable table_Mail = Common.GetTable.GetPurchase_Table();
            for (int i = 0; i < table_Material_PJ.Rows.Count; i++)
            {
                #region  计算重量
                //1 获取MO_NO
                string strMO_NO = table_Material_PJ.Rows[i]["MO_NO"].ToString();
                string strTRANSPORT_NO= table_Material_PJ.Rows[i]["TRANSPORT_NO"].ToString();
                DataRow[] arrRows = table_DN_Temp.Select("TRANSPORT_NO='" + strTRANSPORT_NO + "' and MO_NO='" + strMO_NO + "'");
                //1.1 根据MO_NO在MO_BOM表查询BOM清单
                string sql_BOM = @"SELECT [Package_Mo_Main]
      ,[Package_Mo]
      ,[Line_No]
      ,[Serial_No]
      ,[Levels]
      ,[Demand_TS]
      ,[Material_No]
      ,[Material_Desc]
      ,[Demand_QTY]
      ,[Base_Unit]
      ,[QTY]
      ,[Unit]
  FROM [LamexMESManager].[dbo].[Transfer_MO_BOM]
  where Package_Mo_Main='" + strMO_NO + "'";
                DataTable Table_BOM = BaseBuildSql.GetDataTableBySql(sql_BOM);
                //2 把物料号转换成list
                List<string> list = Table_BOM.AsEnumerable().Select(d => d.Field<string>("Material_No")).ToList();
                //2.2 根据list去查SAP回传的数据
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.AppendFormat(@" AND MATERIAL_NO  IN ('{0}')", string.Join("','", list.ToArray()));
                string sql_SAP = @" SELECT [MATERIAL_NO]
      ,[MATERIAL_VOLUME]
      ,[VOLUME_UNIT]
      ,[MATERIAL_WEIGHT]
      ,[WEIGHT_UNIT]   
      ,[PRODUCT_DIMENSION]
      ,[CREATED_USER]
      ,[CREATED_TIME]
  FROM [LamexMESManager].[dbo].[SA_CBM_WT_From_SAP]
  where ISNULL(DISABLE_FLAG,'')='' " + stringBuilder.ToString() + "";
                DataTable Table_SAP = BaseBuildSql.GetDataTableBySql(sql_SAP);
                //3 根据list去查BOM单位与采购单位转换表
                string sql_Purchase = @"SELECT [Material_No]
      ,[Material_Desc]
      ,[Purchase_Unit_Qty]
      ,[BOM_Unit_Qty]
      ,[Purchase_Unit]
      ,[BOM_Unit]
      ,[Conversion_Rate]
  FROM [LamexMESManager].[dbo].[WH_Mat_BOM_Convert_Purchase]
  where 1=1 " + stringBuilder.ToString() + "";
                DataTable Table_Purchase = BaseBuildSql.GetDataTableBySql(sql_Purchase);
                //4 若步骤3的数据都存在步骤2，则计算；不存在的则发出邮件
                List<string> list1 = Table_Purchase.AsEnumerable().Select(d => d.Field<string>("Material_No")).ToList();
                //4.1 存在--计算
                IEnumerable<string> list_Intersect = list.Intersect(list1);
                decimal dmlUNIT_WEIGHT_Toatl = 0;
                decimal dmlUNIT_VOLUME_Total = 0;
                //订单数量
               decimal order_qty = table_Material_PJ.Rows[i]["ORDER_QTY"].ToString().Length > 0 ? Convert.ToDecimal(table_Material_PJ.Rows[i]["ORDER_QTY"].ToString()) : 0;
                if (order_qty > 0)
                {
                    foreach (var item in list_Intersect)
                    {
                        decimal dmlUNIT_WEIGHT = 0;
                        //BOM表数量
                        decimal qty_BOM = Table_BOM.Select("Material_No='" + item + "'").Length > 0 ?
                            Convert.ToDecimal(Table_BOM.Select("Material_No='" + item + "'").CopyToDataTable().Rows[0]["QTY"].ToString())
                            : 0;
                        //SAP回传数量
                        decimal qty_SAP = Table_SAP.Select("Material_No='" + item + "'").Length>0?
                            Convert.ToDecimal(Table_SAP.Select("Material_No='" + item + "'").CopyToDataTable().Rows[0]["MATERIAL_WEIGHT"].ToString())
                            :0;
                        //SAP回传单位
                        string strWEIGHT_UNIT = Table_SAP.Select("Material_No='" + item + "'").Length>0?
                            Table_SAP.Select("Material_No='" + item + "'").CopyToDataTable().Rows[0]["WEIGHT_UNIT"].ToString():"";
                        DataRow[] unitWeight = table_Unit_Convert.Select("Main_Unit='KG' and Convert_Unit='" + strWEIGHT_UNIT + "'");
                        //采购单位转换率
                        decimal convertRate = Convert.ToDecimal(Table_Purchase.Select("Material_No='" + item + "'").CopyToDataTable().Rows[0]["Conversion_Rate"].ToString());
                        //计算
                        if (unitWeight.Length > 0)
                        {
                            dmlUNIT_WEIGHT = (qty_BOM / order_qty)
                                * (qty_SAP / Convert.ToDecimal(unitWeight.CopyToDataTable().Rows[0]["Convert_Value"].ToString()))
                                * convertRate;
                            dmlUNIT_WEIGHT_Toatl = dmlUNIT_WEIGHT_Toatl + dmlUNIT_WEIGHT;
                        }
                    }
                }
                //4.2 不存在--发邮件
                IEnumerable<string> list_Except = list.Except(list1);
                foreach (var item in list_Except)
                {
                    DataRow dr = table_Mail.NewRow();
                    dr["物料编号"] = item;
                    dr["物料描述"] = Table_BOM.Select("Material_No='" + item + "'").CopyToDataTable().Rows[0]["Material_Desc"].ToString();
                    dr["采购单位数量"] = "";
                    dr["BOM单位数量"] = "";
                    dr["采购单位"] = "";
                    dr["BOM单位"] = "";
                    dr["转换率"] = "";
                    dr["说明"] = "SAP内重量未维护";
                    table_Mail.Rows.Add(dr);
                }

                #endregion
                #region 计算体积
                string sql_SAPCBM = @" SELECT [MATERIAL_NO]
      ,[MATERIAL_VOLUME]
      ,[VOLUME_UNIT]
      ,[MATERIAL_WEIGHT]
      ,[WEIGHT_UNIT]   
      ,[PRODUCT_DIMENSION]
      ,[CREATED_USER]
      ,[CREATED_TIME]
  FROM [LamexMESManager].[dbo].[SA_CBM_WT_From_SAP]
  where ISNULL(DISABLE_FLAG,'')='' AND MATERIAL_NO='" + table_Material_PJ.Rows[i]["MATERIAL_NO"] + "' ";

                DataTable Table_SAPCBM = BaseBuildSql.GetDataTableBySql(sql_SAPCBM);
                //1 有纸箱 则按纸箱算法计算体积
                bool IsCarton = false;
                foreach (var item in cartons)
                {
                    if (item.MO_NO == strMO_NO)
                    {                       
                        foreach (DataRow row in arrRows)
                        {
                            row["CARTON_QTY"] = item.MaterialQTY;
                            dmlUNIT_VOLUME_Total = item.CartonCBM;
                        }
                        IsCarton = true;
                        break;
                    }
                }
                //2 无纸箱 则按SAP取数计算
                if (Table_SAPCBM.Rows.Count > 0)
                {
                    string ProductSize = Table_SAPCBM.Rows[0]["PRODUCT_DIMENSION"].ToString();
                    var sizeList = Calculate.Fuc_CBM.Product_Size_CBM(ProductSize, 0, 0, 0);                 
                    foreach (DataRow row in arrRows)
                    {
                        row["VOLUME_LENGTH"] = sizeList.Item1;
                        row["VOLUME_WIDTH"] = sizeList.Item2;
                        row["VOLUME_HEIGHT"] = sizeList.Item3;
                    }
                   
                    if (!IsCarton)
                    {
                        string strVOLUME_UNIT = Table_SAPCBM.Rows[0]["VOLUME_UNIT"].ToString().Trim().ToUpper();
                        DataRow[] unitVolume = table_Unit_Convert.Select("Main_Unit='M3' and Convert_Unit='" + strVOLUME_UNIT + "'");
                        if (unitVolume.Length > 0)
                            dmlUNIT_VOLUME_Total = Convert.ToDecimal(Table_SAPCBM.Rows[0]["MATERIAL_VOLUME"].ToString()) /
                                   Convert.ToDecimal(unitVolume.CopyToDataTable().Rows[0]["Convert_Value"].ToString());

                    }
                }
                #endregion
                //5 计算为0的写到错误日志表
                if (list_Except.ToList().Count>0)
                    Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(table_Material_PJ.Rows[i]["TRANSPORT_NO"].ToString(), table_Material_PJ.Rows[i]["MO_NO"].ToString(),
                         table_Material_PJ.Rows[i]["MATERIAL_NO"].ToString(), table_Material_PJ.Rows[i]["MATERIAL_DESC"].ToString(),
                   "", "", "", "", "", "SAP内重量未维护");

                //6 把结果更新到DN表
                foreach (DataRow row in arrRows)
                {
                    row["UNIT_WEIGHT"] = dmlUNIT_WEIGHT_Toatl;
                    row["UNIT_VOLUME"] = dmlUNIT_VOLUME_Total;
                    row["CAL_TYPE"] = "SAP取数";
                }
            }
            // 把不存在的发邮件出来
            if (table_Mail.Rows.Count > 0)
            {
                string[] strAdress = { "Bill.Luo@lamex.com.hk" };
                string[] strCC = { "zhongjie.yang@lamex.com.hk" };//抄送
                Common.MailHelper.SendMail_2(strAdress, strCC, table_Mail);
            }
            return table_DN_Temp;
        }

        //非OEM产品CBM及重量计算
        public static DataTable Get_Table_NoOEM_Lamint(DataTable table_DN_Temp, DataTable table_Material_NoOEM)
        {
            List<string> list = new List<string>();
            for (int i = 0; i < table_Material_NoOEM.Rows.Count; i++)
            {
                if (!string.IsNullOrEmpty(table_Material_NoOEM.Rows[i]["MATERIAL_NO"].ToString().Trim()))
                    list.Add(table_Material_NoOEM.Rows[i]["MATERIAL_NO"].ToString().Trim());
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendFormat(@" AND MATERIAL_NO  IN ('{0}')", string.Join("','", list.ToArray()));
            DataTable dataTable = new DataTable();
            string sql = @" SELECT [MATERIAL_NO]
      ,[MATERIAL_VOLUME]
      ,[VOLUME_UNIT]
      ,[MATERIAL_WEIGHT]
      ,[WEIGHT_UNIT]   
      ,[PRODUCT_DIMENSION]
      ,[CREATED_USER]
      ,[CREATED_TIME]
  FROM [LamexMESManager].[dbo].[SA_CBM_WT_From_SAP]
  where ISNULL(DISABLE_FLAG,'')='' " + stringBuilder.ToString() + "";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            if (dataTable.Rows.Count > 0)
            {
                //更新【临时表DN】物料号相同的记录
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    string strMaterialNo = dataTable.Rows[i]["MATERIAL_NO"].ToString();
                    DataRow[] arrRows = table_DN_Temp.Select("MATERIAL_NO='" + strMaterialNo + "'");
                    //调用[ Fuc_CBM]
                    var sizeList = Calculate.Fuc_CBM.Product_Size_CBM(dataTable.Rows[i]["PRODUCT_DIMENSION"].ToString(),20,20,5);
                    foreach (DataRow row in arrRows)
                    {
                        row["VOLUME_LENGTH"] = sizeList.Item1;
                        row["VOLUME_WIDTH"] = sizeList.Item2;
                        row["VOLUME_HEIGHT"] = sizeList.Item3;
                        row["UNIT_VOLUME"] = sizeList.Item4;
                        row["CAL_TYPE"] = "SAP取数";
                        //CBM和重量为0时插入错误日志表
                        if (sizeList.Item4 == 0)
                            Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(row["TRANSPORT_NO"].ToString(), row["MO_NO"].ToString(), row["MATERIAL_NO"].ToString(), row["MATERIAL_DESC"].ToString(),
                      "", "", sizeList.Item1.ToString(), sizeList.Item2.ToString(), sizeList.Item3.ToString(), "SAP取数为0");

                    }

                }

            }
            return table_DN_Temp;
        }

        //非OEM产品非桌子BOM数据表[MO_NO]
        public static DataTable Get_Table_NoOEM_BOM(DataTable table_Material_OEM)
        {
            List<string> list = new List<string>();
            for (int i = 0; i < table_Material_OEM.Rows.Count; i++)
            {
                if (!string.IsNullOrEmpty(table_Material_OEM.Rows[i]["MO_NO"].ToString().Trim()))
                    list.Add(table_Material_OEM.Rows[i]["MO_NO"].ToString().Trim());
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendFormat(@" AND Package_Mo_Main  IN ('{0}')", string.Join("','", list.ToArray()));
            DataTable dataTable = new DataTable();
            string sql = @" SELECT  [Package_Mo_Main]
      ,[Package_Mo]
      ,[Material_No]
      ,[Material_Desc]
      ,[Demand_QTY]
      ,[Base_Unit]
      ,[Levels]
      ,'' as Weight 
      ,'' as Total_Weight
      ,'' as Weight_Type
  FROM [LamexMESManager].[dbo].[Transfer_MO_BOM] WHERE 1=1" + stringBuilder.ToString() + "";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);

            return dataTable;
        }
        //非OEM产品非桌子BOM数据表[MaterailNo]
        public static DataTable Get_Table_NoOEM_BOM_MaterialNo(DataTable table_Material_OEM)
        {
            List<string> list = new List<string>();
            for (int i = 0; i < table_Material_OEM.Rows.Count; i++)
            {
                if (!string.IsNullOrEmpty(table_Material_OEM.Rows[i]["MATERIAL_NO"].ToString().Trim()))
                    list.Add(table_Material_OEM.Rows[i]["MATERIAL_NO"].ToString().Trim());
            }
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendFormat(@" AND Material_No  IN ('{0}')", string.Join("','", list.ToArray()));
            DataTable dataTable = new DataTable();
            string sql = @" SELECT  [Package_Mo_Main]
      ,[Package_Mo]
      ,[Material_No]
      ,[Material_Desc]
      ,[Demand_QTY]
  FROM [LamexMESManager].[dbo].[Transfer_MO_BOM] WHERE 1=1" + stringBuilder.ToString() + "";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);

            return dataTable;
        }

        //非OEM产品非桌子CBM及重量计算--纸箱包装
        public static DataTable Get_Table_NoOEM_NoLamint(List<CartonMaterial> cartons, DataTable table_DN_Temp)
        {
            foreach (var item in cartons)
            {
                DataRow[] arrRows = table_DN_Temp.Select("MO_NO='" + item.MO_NO + "'");
                foreach (DataRow row in arrRows)
                {
                    row["CARTON_QTY"] = item.MaterialQTY;
                    row["UNIT_VOLUME"] = item.CartonCBM;
                    row["VOLUME_LENGTH"] = item.VOLUME_LENGTH;
                    row["VOLUME_WIDTH"] = item.VOLUME_WIDTH;
                    row["VOLUME_HEIGHT"] = item.VOLUME_HEIGHT;
                    row["CAL_TYPE"] = "BOM纸箱";
                    //CBM和重量为0时插入错误日志表
                    if (item.CartonCBM == 0)
                        Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(row["TRANSPORT_NO"].ToString(), row["MO_NO"].ToString(), row["MATERIAL_NO"].ToString(), row["MATERIAL_DESC"].ToString(),
                       "", "", item.VOLUME_LENGTH.ToString(), item.VOLUME_WIDTH.ToString(), item.VOLUME_HEIGHT.ToString(), "BOM纸箱为0");

                }
            }
            return table_DN_Temp;
        }
        //非OEM产品非桌子CBM及重量计算--其他包装
        public static DataTable Get_CutSizeTable(DataTable table_DN_Temp, DataTable dt_MO_Temp, DataTable dt_Mo_Main, out DataTable dt_Temp)
        {
            DataTable table = Common.GetTable.Get_CutSize_Temp();//保存铝材、板材清单回传给前台
            for (int k = 0; k < dt_Mo_Main.Rows.Count; k++)
            {
                decimal order_qty = 0;//订单数量
                DataTable dt_orderQty = table_DN_Temp.Select("MO_NO='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'").CopyToDataTable();
                order_qty = dt_orderQty.Rows[0]["ORDER_QTY"].ToString().Length > 0 ? Convert.ToDecimal(dt_orderQty.Rows[0]["ORDER_QTY"].ToString()) : 0;
                if (dt_MO_Temp.Select("Package_Mo_Main='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'").Length > 0)
                {
                    DataTable dt_MO_Temp1 = dt_MO_Temp.Select("Package_Mo_Main='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'").CopyToDataTable();
                    List<string> list_1 = new List<string>();
                    for (int j = 0; j < dt_MO_Temp1.Rows.Count; j++)
                    {
                        if (!string.IsNullOrEmpty(dt_MO_Temp1.Rows[j]["Material_No"].ToString().Trim()))
                            list_1.Add(dt_MO_Temp1.Rows[j]["Material_No"].ToString().Trim());
                    }

                    StringBuilder stringBuilder = new StringBuilder();
                    stringBuilder.AppendFormat(@" AND PART_NO  IN ('{0}')", string.Join("','", list_1.ToArray()));
                    DataTable dataTable = new DataTable();
                    string sql = @" SELECT [PART_NO]
                             ,ORDER_NUMBER
                             ,[SNG_CUT_SIZE_X]
                             ,[SNG_CUT_SIZE_Y]
                             ,[THICKNESS]
                             ,MATERIAL_CODE
                             ,ROUTING_CODE
                         FROM [LamexMESManager].[dbo].[SA_CBM_WT_Cutsize] 
                        WHERE 1=1 " + stringBuilder.ToString() + "";
                    dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
                    //有开料尺寸
                    if (dataTable.Rows.Count > 0)
                    {
                        DataTable dt_CutSize = Common.GetTable.Get_CutSize_Temp();
                        var query = from a in dataTable.AsEnumerable()
                                    from b in dt_MO_Temp1.AsEnumerable()
                                    where a.Field<string>("PART_NO") == b.Field<string>("Material_No")
                                    select new
                                    {
                                        Material_No = a.Field<string>("PART_NO"),
                                        ORDER_NUMBER = a.Field<string>("ORDER_NUMBER"),
                                        MATERIAL_DESC = b.Field<string>("Material_Desc"),
                                        Carton_Qty = b.Field<decimal>("Demand_QTY"),
                                        Length = a.Field<decimal>("SNG_CUT_SIZE_X"),
                                        Width = a.Field<decimal>("SNG_CUT_SIZE_Y"),
                                        Thick = a.Field<decimal>("THICKNESS"),
                                        Measure_Area = a.Field<decimal>("SNG_CUT_SIZE_X") * a.Field<decimal>("SNG_CUT_SIZE_Y"),
                                        ROUTING_CODE = a.Field<string>("ROUTING_CODE"),
                                        MATERIAL_CODE = a.Field<string>("MATERIAL_CODE"),
                                        Base_Unit = b.Field<string>("Base_Unit"),
                                    };
                        foreach (var obj in query)
                        {
                            dt_CutSize.Rows.Add(obj.Material_No, obj.ORDER_NUMBER, obj.MATERIAL_DESC, obj.Carton_Qty, obj.Length, obj.Width, obj.Thick, obj.Measure_Area, obj.ROUTING_CODE, obj.MATERIAL_CODE, obj.Base_Unit);
                        }
                        if (dt_CutSize.Rows.Count > 0)
                        {
                            table.Merge(dt_CutSize);
                            decimal qty = Convert.ToDecimal(dt_CutSize.Compute("SUM(Carton_Qty)", ""));//数量                        
                            if (qty == 1)
                            {
                                decimal volume = 0;
                                decimal Length = Convert.ToDecimal(dt_CutSize.Rows[0]["Length"].ToString()) + 20;
                                decimal Width = Convert.ToDecimal(dt_CutSize.Rows[0]["Width"].ToString()) + 20;
                                decimal Thick = Convert.ToDecimal(dt_CutSize.Rows[0]["Thick"].ToString()) + 5;
                                volume = Length * Width * Thick / 1000000000;
                                DataRow[] arrRows = table_DN_Temp.Select("MO_NO='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'");
                                foreach (DataRow row in arrRows)
                                {
                                    row["UNIT_VOLUME"] = volume;
                                    row["VOLUME_LENGTH"] = Length;
                                    row["VOLUME_WIDTH"] = Width;
                                    row["VOLUME_HEIGHT"] = Thick;
                                    row["CAL_TYPE"] = "开料尺寸";
                                    //CBM和重量为0时插入错误日志表
                                    if (volume == 0)
                                        Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(row["TRANSPORT_NO"].ToString(), row["MO_NO"].ToString(), row["MATERIAL_NO"].ToString(), row["MATERIAL_DESC"].ToString(),
                   "", "", Length.ToString(), Width.ToString(), Thick.ToString(), "开料尺寸为0");

                                }

                            }
                            else
                            {
                                decimal volume_small = 0, volume_large = 0;
                                decimal Length = 0, Width = 0, Thick = 0;
                                decimal sums_thinck = 0, sums_thinck_D24 = 0;
                                decimal sums_thinck_NOD24 = 0;//保存大板厚度
                                //if (dt_CutSize.Compute("sum(Carton_Qty)", "ISNULL(ROUTING_CODE,'')  not like '%D24%'").ToString().Length > 0)
                                //    sums_qty = Convert.ToDecimal(dt_CutSize.Compute("sum(Carton_Qty)", "ISNULL(ROUTING_CODE,'')  not like '%D24%'"));//不过小板线的数量和                               
                                if (dt_CutSize.Compute("sum(Thick)", "ISNULL(ROUTING_CODE,'') not like '%D24%'").ToString().Length > 0)
                                    sums_thinck = Convert.ToDecimal(dt_CutSize.Compute("sum(Thick)", "ISNULL(ROUTING_CODE,'') not like '%D24%'"));//不过小板线的厚度和
                                if (dt_CutSize.Compute("sum(Thick)", "ISNULL(ROUTING_CODE,'')  like '%D24%'").ToString().Length > 0)
                                    sums_thinck_D24 = Convert.ToDecimal(dt_CutSize.Compute("sum(Thick)", "ISNULL(ROUTING_CODE,'')  like '%D24%'"));//过小板线的厚度和
                                string max_area = dt_CutSize.Compute("max(Measure_Area)", "ISNULL(ROUTING_CODE,'') not like '%D24%'").ToString().Length > 0 ?
                                    dt_CutSize.Compute("max(Measure_Area)", "ISNULL(ROUTING_CODE,'') not like '%D24%'").ToString() : "0";//不过小板线最大面积
                                List<MaterialSize> sizes = new List<MaterialSize>();
                                List<MaterialSize> sizesList = new List<MaterialSize>();
                                int boardBig = 0;//定义是否有多块大板
                                for (int s = 0; s < dt_CutSize.Rows.Count; s++)
                                {
                                    //经过小板线
                                    if (dt_CutSize.Rows[s]["ROUTING_CODE"].ToString().Contains("D24"))
                                    {
                                        decimal CartonQty = Convert.ToDecimal(dt_CutSize.Rows[s]["Carton_Qty"].ToString());
                                        decimal LengthTemp = Convert.ToDecimal(dt_CutSize.Rows[s]["Length"].ToString());
                                        decimal WidthTemp = Convert.ToDecimal(dt_CutSize.Rows[s]["Width"].ToString());
                                        decimal ThickTemp = Convert.ToDecimal(dt_CutSize.Rows[s]["Thick"].ToString());
                                        decimal volume_small_temp = (LengthTemp + 20) * (WidthTemp + 20) * (ThickTemp + 5) * CartonQty / 1000000000;
                                        if (order_qty > 0)
                                            volume_small = volume_small + volume_small_temp / order_qty;

                                        MaterialSize materialSize = new MaterialSize();
                                        materialSize.VOLUME_LENGTH = LengthTemp;
                                        materialSize.VOLUME_WIDTH = WidthTemp;
                                        materialSize.VOLUME_HEIGHT = ThickTemp;
                                        materialSize.CartonCBM = volume_small_temp;
                                        sizes.Add(materialSize);
                                    }
                                    //不经过小板线
                                    else
                                    {
                                        //求出厚度*发料数
                                        decimal CartonQty = Convert.ToDecimal(dt_CutSize.Rows[s]["Carton_Qty"].ToString());
                                        decimal ThickTemp = Convert.ToDecimal(dt_CutSize.Rows[s]["Thick"].ToString());
                                        if (order_qty > 0)
                                            sums_thinck_NOD24 = sums_thinck_NOD24 + ((ThickTemp + 10) * CartonQty / order_qty);
                                        //是否有多块板
                                        boardBig++;
                                    }
                                }
                                //得到经过小板线最大面积的长宽厚
                                if (sizes.Count > 0)
                                {
                                    var max_cbm = sizes.First(p => p.CartonCBM == sizes.Max(p2 => p2.CartonCBM));
                                    sizesList.Add(max_cbm);
                                }

                                //不经过小板线-取最大面积的长和宽
                                DataRow[] dataRows = dt_CutSize.Select("Measure_Area=" + max_area + "");
                                if (dataRows.Length > 0)
                                {
                                    DataTable dataTable1 = dataRows.CopyToDataTable();
                                    decimal LengthTemp = Convert.ToDecimal(dataTable1.Rows[0]["Length"].ToString());
                                    decimal WidthTemp = Convert.ToDecimal(dataTable1.Rows[0]["Width"].ToString());
                                    //if (boardBig > 1)
                                    volume_large = (LengthTemp + 20) * (WidthTemp + 20) * sums_thinck_NOD24 / 1000000000;
                                    //else
                                    //    volume_large = (LengthTemp + 20) * (WidthTemp + 20) * (sums_thinck_NOD24 + 5) / 1000000000;

                                    Length = LengthTemp;
                                    Width = WidthTemp;
                                    Thick = sums_thinck + sums_thinck_D24;
                                }
                                //经过小板线的长*宽*高
                                else
                                {
                                    foreach (var size in sizesList)
                                    {
                                        Length = size.VOLUME_LENGTH;
                                        Width = size.VOLUME_WIDTH;
                                        Thick = size.VOLUME_HEIGHT;
                                    }
                                }
                                DataRow[] arrRows = table_DN_Temp.Select("MO_NO='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'");
                                foreach (DataRow row in arrRows)
                                {
                                    row["UNIT_VOLUME"] = volume_small + volume_large;
                                    row["VOLUME_LENGTH"] = Length;
                                    row["VOLUME_WIDTH"] = Width;
                                    row["VOLUME_HEIGHT"] = Thick;
                                    row["CAL_TYPE"] = "开料尺寸";
                                    //CBM和重量为0时插入错误日志表
                                    if (volume_small + volume_large == 0)
                                        Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(row["TRANSPORT_NO"].ToString(), row["MO_NO"].ToString(), row["MATERIAL_NO"].ToString(), row["MATERIAL_DESC"].ToString(),
                    "", "", Length.ToString(), Width.ToString(), Thick.ToString(), "开料尺寸为0");
                                }
                            }
                        }
                    }
                    //无开料尺寸
                    else
                    {
                        DataRow[] arrRows = table_DN_Temp.Select("MO_NO='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'");
                        foreach (DataRow row in arrRows)
                        {
                            row["UNIT_VOLUME"] = 0;
                            row["VOLUME_LENGTH"] = 0;
                            row["VOLUME_WIDTH"] = 0;
                            row["VOLUME_HEIGHT"] = 0;
                            row["CAL_TYPE"] = "开料尺寸";
                            //CBM和重量为0时插入错误日志表                         
                            Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(row["TRANSPORT_NO"].ToString(), row["MO_NO"].ToString(), row["MATERIAL_NO"].ToString(), row["MATERIAL_DESC"].ToString(),
       "", "", "", "", "", "无开料尺寸");

                        }
                    }
                }
            }
            dt_Temp = table;//回写到前台
            return table_DN_Temp;
        }

        //非OEM非配件产品重量计算--称重计重法
        public static DataTable Get_Table_NoOEM_Weight(DataTable table_DN_Temp, DataTable dt_Mo_Main,DataTable dt_BOM)
        {
            List<string> list = new List<string>();
            for (int i = 0; i < dt_Mo_Main.Rows.Count; i++)
            {
                if (!string.IsNullOrEmpty(dt_Mo_Main.Rows[i]["MO_NO"].ToString().Trim()))
                    list.Add(dt_Mo_Main.Rows[i]["MO_NO"].ToString().Trim());
            }
            StringBuilder stringBuilder = new StringBuilder();
            if (list.Count > 0)
                stringBuilder.AppendFormat(@" AND ProdOrder  IN ('{0}')", string.Join("','", list.ToArray()));
            DataTable dataTable = new DataTable();
            string sql = @" SELECT ProdOrder,  AVG(Scale_weight)/AVG(InsideQty) AS Scale_weight FROM [LamexMESManager].[dbo].[Transfer_Scanning_record]
WHERE 1=1" + stringBuilder.ToString() + "  group by ProdOrder";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                DataRow[] arrRows = table_DN_Temp.Select("MO_NO='" + dataTable.Rows[i]["ProdOrder"].ToString() + "'");
                foreach (DataRow row in arrRows)
                {
                    row["UNIT_WEIGHT"] = Convert.ToDecimal(dataTable.Rows[i]["Scale_weight"].ToString());
                    row["CAL_TYPE"] = "称重计重" + " " + row["CAL_TYPE"];
                    //CBM和重量为0时插入错误日志表
                    if (Convert.ToDecimal(dataTable.Rows[i]["Scale_weight"].ToString()) == 0)
                        Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(row["TRANSPORT_NO"].ToString(), row["MO_NO"].ToString(), row["MATERIAL_NO"].ToString(), row["MATERIAL_DESC"].ToString(),
                      "", "", "", "", "", "称重计重为0");

                }
                //计算单个BOM物料的单重和总重，返回给前台
                if (dt_BOM.Select("Package_Mo_Main='" + dataTable.Rows[i]["ProdOrder"].ToString() + "'").Length > 0)
                {
                    DataTable dataTable1 = dt_BOM.Select("Package_Mo_Main='" + dataTable.Rows[i]["ProdOrder"].ToString() + "'").CopyToDataTable();
                    for (int k = 0; k < dataTable1.Rows.Count; k++)
                    {
                        DataRow[] arrRowsBom = dt_BOM.Select("MATERIAL_NO='" + dataTable1.Rows[k]["Material_No"].ToString() + "'");
                        foreach (DataRow row in arrRowsBom)
                        {
                            row["Total_Weight"] = dataTable.Rows[i]["Scale_weight"].ToString();
                            row["Weight_Type"] = "称重计重法";
                        }
                    }
               
                }
            }
            return table_DN_Temp;
        }
        //非OEM非配件产品重量计算--BOM计重法
        public static DataTable Get_Table_NoOEM_BOM_Weight(DataTable table_DN_Temp, DataTable dt_MO_Temp, DataTable dt_Mo_Main, DataTable dt_Weight, DataTable dt_CBM_WT_From_SAP, DataTable dt_BOM, out DataTable dt_Temp)
        {
            DataTable table = Common.GetTable.Get_CutSize_Temp();//保存铝材、板材清单回传给前台
            for (int k = 0; k < dt_Mo_Main.Rows.Count; k++)
            {
                decimal bom_weight = 0;//BOM体重
                decimal board_weight_sum = 0;//板材重量
                decimal pl_weight_sum = 0;//铝材重量
                decimal sap_weight_sum = 0;//物料体重（包括原材料和采购）
                decimal other_weight = 0;//其他重量
                decimal weight_tpf_sum = 0;//铁屏风重量
                decimal order_qty = 0;//订单数量
                DataTable dataTable1 = table_DN_Temp.Select("MO_NO='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'").CopyToDataTable();
                order_qty = dataTable1.Rows[0]["ORDER_QTY"].ToString().Length > 0 ? Convert.ToDecimal(dataTable1.Rows[0]["ORDER_QTY"].ToString()) : 0;
                if (dt_MO_Temp.Select("Package_Mo_Main='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'").Length > 0)
                {

                    DataTable dt_MO_Temp1 = dt_MO_Temp.Select("Package_Mo_Main='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'").CopyToDataTable();
                    List<string> list_1 = new List<string>();
                    for (int j = 0; j < dt_MO_Temp1.Rows.Count; j++)
                    {
                        if (!string.IsNullOrEmpty(dt_MO_Temp1.Rows[j]["Material_No"].ToString().Trim()))
                            list_1.Add(dt_MO_Temp1.Rows[j]["Material_No"].ToString().Trim());
                    }

                    StringBuilder stringBuilder = new StringBuilder();
                    stringBuilder.AppendFormat(@" AND PART_NO  IN ('{0}')", string.Join("','", list_1.ToArray()));
                    DataTable dataTable = new DataTable();
                    string sql = @" SELECT [PART_NO]
                             ,ORDER_NUMBER
                             ,[SNG_CUT_SIZE_X]
                             ,[SNG_CUT_SIZE_Y]
                             ,[THICKNESS]
                             ,MATERIAL_CODE
                             ,ROUTING_CODE
                         FROM [LamexMESManager].[dbo].[SA_CBM_WT_Cutsize] 
                        WHERE 1=1 " + stringBuilder.ToString() + "";
                    dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);

                    //板材/铝材计重
                    List<string> list_2 = new List<string>();
                    if (dataTable.Rows.Count > 0)
                    {                      
                        DataTable dt_CutSize = Common.GetTable.Get_CutSize_Temp();
                        var query = from a in dataTable.AsEnumerable()
                                    from b in dt_MO_Temp1.AsEnumerable()
                                    where a.Field<string>("PART_NO") == b.Field<string>("Material_No")
                                    select new
                                    {
                                        Material_No = a.Field<string>("PART_NO"),
                                        ORDER_NUMBER=a.Field<string>("ORDER_NUMBER"),
                                        MATERIAL_DESC = b.Field<string>("Material_Desc"),
                                        Carton_Qty = b.Field<decimal>("Demand_QTY"),
                                        Length = a.Field<decimal>("SNG_CUT_SIZE_X"),
                                        Width = a.Field<decimal>("SNG_CUT_SIZE_Y"),
                                        Thick = a.Field<decimal>("THICKNESS"),
                                        Measure_Area = a.Field<decimal>("SNG_CUT_SIZE_X") * a.Field<decimal>("SNG_CUT_SIZE_Y"),
                                        ROUTING_CODE = a.Field<string>("ROUTING_CODE"),
                                        MATERIAL_CODE = a.Field<string>("MATERIAL_CODE"),
                                        Base_Unit = b.Field<string>("Base_Unit"),
                                    };
                        foreach (var obj in query)
                        {
                            dt_CutSize.Rows.Add(obj.Material_No, obj.ORDER_NUMBER, obj.MATERIAL_DESC, obj.Carton_Qty, obj.Length, obj.Width, obj.Thick, obj.Measure_Area, obj.ROUTING_CODE, obj.MATERIAL_CODE, obj.Base_Unit);
                        }
                        table.Merge(dt_CutSize);

                        for (int i = 0; i < dt_CutSize.Rows.Count; i++)
                        {
                            string OrderNumber = dt_CutSize.Rows[i]["ORDER_NUMBER"].ToString();
                            string Material_No = dt_CutSize.Rows[i]["Material_No"].ToString();
                            decimal Carton_Qty = Convert.ToDecimal(dt_CutSize.Rows[i]["Carton_Qty"].ToString());//发料数量
                            //板材
                            if (OrderNumber.StartsWith("DK") || OrderNumber.StartsWith("PN"))
                            {
                                //保存板材BOM的物料
                                list_2.Add(Material_No);

                                decimal weight_per_m2 = 0;
                                decimal board_weight = 0;
                                decimal Length = dt_CutSize.Rows[i]["Length"].ToString().Length > 0 ? Convert.ToDecimal(dt_CutSize.Rows[i]["Length"].ToString()) : 0;
                                decimal Width = dt_CutSize.Rows[i]["Width"].ToString().Length > 0 ? Convert.ToDecimal(dt_CutSize.Rows[i]["Width"].ToString()) : 0;
                                decimal Thick = dt_CutSize.Rows[i]["Thick"].ToString().Length > 0 ? Convert.ToDecimal(dt_CutSize.Rows[i]["Thick"].ToString()) : 0;
                                //[DECOR]不为空且包含有"-"字符
                                if (!string.IsNullOrEmpty(dt_CutSize.Rows[i]["MATERIAL_CODE"].ToString())
                                    && dt_CutSize.Rows[i]["MATERIAL_CODE"].ToString().Contains("-"))
                                {
                                    string decor = dt_CutSize.Rows[i]["MATERIAL_CODE"].ToString();
                                    string[] decorList = decor.Split('-');
                                    Array.Reverse(decorList);
                                    string mat_type = decorList[0];
                                    decimal mat_thick = Convert.ToDecimal(decorList[1]);
                                    if (dt_Weight.Select("MAT_TYPE='" + mat_type + "' and MAT_THICK=" + mat_thick + "").Length > 0)
                                    {
                                        weight_per_m2 = Convert.ToDecimal(dt_Weight.Select("MAT_TYPE='" + mat_type + "' and MAT_THICK=" + mat_thick + "").CopyToDataTable().Rows[0]["WEIGHT_PER_M2"].ToString());

                                    }
                                }
                                else if (string.IsNullOrEmpty(dt_CutSize.Rows[i]["MATERIAL_CODE"].ToString())
                                    || !dt_CutSize.Rows[i]["MATERIAL_CODE"].ToString().Contains("-"))
                                {
                                    string mat_type = "MFC";
                                    decimal mat_thick = Thick;
                                    if (dt_Weight.Select("MAT_TYPE='" + mat_type + "' and MAT_THICK=" + mat_thick + "").Length > 0)
                                    {
                                        weight_per_m2 = Convert.ToDecimal(dt_Weight.Select("MAT_TYPE='" + mat_type + "' and MAT_THICK=" + mat_thick + "").CopyToDataTable().Rows[0]["WEIGHT_PER_M2"].ToString());

                                    }
                                }
                                if (order_qty > 0)
                                    board_weight = (Length * Width / 1000000) * weight_per_m2 * Carton_Qty / order_qty;
                                board_weight_sum = board_weight_sum + board_weight;

                                //计算单个BOM物料的单重和总重，返回给前台
                                DataRow[] arrRowsBom = dt_BOM.Select("MATERIAL_NO='" + dt_CutSize.Rows[i]["Material_No"].ToString() + "' and " +
                                    "Package_Mo_Main='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'");
                                foreach (DataRow row in arrRowsBom)
                                {

                                    row["Weight"] = (Length * Width / 1000000) * weight_per_m2;
                                    row["Total_Weight"] = board_weight;
                                    row["Weight_Type"] = "BOM计重法-板材计重";
                                    //CBM和重量为0时插入错误日志表
                                    if (board_weight == 0)
                                        Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(dataTable1.Rows[0]["TRANSPORT_NO"].ToString(), dataTable1.Rows[0]["MO_NO"].ToString(), dataTable1.Rows[0]["MATERIAL_NO"].ToString(), dataTable1.Rows[0]["MATERIAL_DESC"].ToString(),
                        row["Material_No"].ToString(), row["Material_Desc"].ToString(), Length.ToString(), Width.ToString(), Thick.ToString(), "板材计重为0");

                                }

                            }
                            //铝材
                            if (OrderNumber.StartsWith("PL"))
                            {
                                //保存铝材BOM的物料
                                list_2.Add(Material_No);

                                //物料号为3开头
                                if(Material_No.StartsWith("3"))
                                {
                                    List<string> listLevel = new List<string>();//保存符合条件的物料号
                                    string level = "";//同层次区间物料
                                    //取该物料号MO_BOM表的同层物料
                                    for (int n = 0; n < dt_MO_Temp1.Rows.Count; n++)
                                    {                                                                           
                                        string strMaterial = dt_MO_Temp1.Rows[n]["Material_No"].ToString();
                                        if (strMaterial == Material_No)
                                        {
                                            level = dt_MO_Temp1.Rows[n]["Levels"].ToString();
                                            continue;
                                        }
                                        if (level.Length > 0)
                                        {
                                            if (dt_MO_Temp1.Rows[n]["Levels"].ToString() != level)
                                                listLevel.Add(strMaterial);
                                            else
                                                break;
                                        }
                                    }

                                    decimal pl_weight = 0;
                                    decimal pl_weight_psum = 0;
                                    decimal pl_weight_p = 0;
                                    //用符合条件的物料号去查询[SAP数据表]和[开料尺寸表]
                                    foreach (var item in listLevel)
                                    {
                                        if (dt_CBM_WT_From_SAP.Select("MATERIAL_NO='" + item + "'").Length > 0)
                                        {
                                            //取得[SAP数据表]
                                            DataTable dt_from_sap_Temp = dt_CBM_WT_From_SAP.Select("MATERIAL_NO='" + item + "'").CopyToDataTable();
                                            string productSize = dt_from_sap_Temp.Rows[0]["PRODUCT_DIMENSION"].ToString();
                                            string stockLocation = dt_from_sap_Temp.Rows[0]["STOCK_LOCATION"].ToString();//库存地点
                                            //经过库存地点FP01的物料号才往下执行
                                            if (stockLocation.Trim().ToUpper() != "FP01")
                                                continue;
                                            decimal length_SAP = Calculate.Fuc_CBM.Product_Size_Length(productSize);
                                            decimal weight_SAP = Convert.ToDecimal(dt_from_sap_Temp.Rows[0]["MATERIAL_WEIGHT"].ToString());
                                            //取得[开料尺寸表]-原物料号
                                            decimal length_Cutting = 0;
                                            if (dt_CutSize.Select("Material_No='" + Material_No + "'").Length > 0)
                                            {
                                                DataTable dt_length = dt_CutSize.Select("Material_No='" + Material_No + "'").CopyToDataTable();
                                                length_Cutting = Convert.ToDecimal(dt_length.Rows[0]["Length"].ToString());
                                            }
                                            if (length_SAP >0)
                                                pl_weight = weight_SAP / length_SAP * length_Cutting;
                                            //pl_weight大于3kg,需保存起来,回传前台
                                            //......
                                            //pl_weight_sum = pl_weight_sum + pl_weight;
                                            pl_weight_p = pl_weight_p + pl_weight;
                                        }
                                    }
                                    if (order_qty > 0)
                                    {
                                        //pl_weight_sum = pl_weight_sum * Carton_Qty / order_qty;
                                        pl_weight_psum= pl_weight_p * Carton_Qty / order_qty;
                                        pl_weight_sum = pl_weight_sum + pl_weight_psum;
                                    }



                                    //计算单个BOM物料的单重和总重，返回给前台
                                    DataRow[] arrRowsBom = dt_BOM.Select("MATERIAL_NO='" + dt_CutSize.Rows[i]["Material_No"].ToString() + "' and " +
                                        "Package_Mo_Main='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'");
                                    foreach (DataRow row in arrRowsBom)
                                    {
                                        row["Weight"] = pl_weight_p;
                                        row["Total_Weight"] = pl_weight_psum;//需要乘数量
                                        row["Weight_Type"] = "BOM计重法-铝材计重";
                                        //CBM和重量为0时插入错误日志表
                                        if (pl_weight_psum == 0)
                                            Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(dataTable1.Rows[0]["TRANSPORT_NO"].ToString(), dataTable1.Rows[0]["MO_NO"].ToString(), dataTable1.Rows[0]["MATERIAL_NO"].ToString(), dataTable1.Rows[0]["MATERIAL_DESC"].ToString(),
                    row["Material_No"].ToString(), row["Material_Desc"].ToString(), dt_CutSize.Rows[i]["Length"].ToString(), dt_CutSize.Rows[i]["Width"].ToString(), dt_CutSize.Rows[i]["Thick"].ToString(), "铝材计重为0");

                                    }
                                }
                            }
                        }
                    }
                    //去重板材、铝材的BOM物料，计算外发和玻璃的重量
                    List<string> list_3 = new List<string>();
                    IEnumerable<string> exceptLs = list_1.Except(list_2);
                    //玻璃、外发3计重
                    if (exceptLs.ToList().Count > 0)
                    {                     
                        foreach (var item in exceptLs)
                        {
                            //bool flag = false;//是否有计算                          
                            decimal sap_weight_glass = 0;//玻璃重
                            decimal sap_weight_f = 0;//外发重
                            string strMaterialNo = item;
                            string strWeight = "";

                            decimal Carton_Qty = dt_MO_Temp1.Select("Material_No='" + strMaterialNo + "'").Length > 0 ?
                               Convert.ToDecimal(dt_MO_Temp1.Select("Material_No='" + strMaterialNo + "'").CopyToDataTable().Compute("sum(Demand_QTY)", ""))
                                : 0;
                            //玻璃计重
                            if (dt_MO_Temp1.Select("Material_No='" + strMaterialNo + "'").CopyToDataTable().Rows[0]["Material_Desc"].ToString().Contains("玻璃"))
                            {
                                if (dt_CBM_WT_From_SAP.Select("MATERIAL_NO='" + strMaterialNo + "'").Length > 0)
                                {
                                    list_3.Add(strMaterialNo);
                                    DataTable dt_from_sap_Temp = dt_CBM_WT_From_SAP.Select("MATERIAL_NO='" + strMaterialNo + "'").CopyToDataTable();
                                    strWeight = dt_from_sap_Temp.Rows[0]["MATERIAL_WEIGHT"].ToString();
                                    if (order_qty > 0)
                                        sap_weight_glass = Convert.ToDecimal(strWeight) * Carton_Qty / order_qty;
                                    //计算单个BOM物料的单重和总重，返回给前台
                                    DataRow[] arrRowsBom = dt_BOM.Select("MATERIAL_NO='" + strMaterialNo + "' and " +
                                        "Package_Mo_Main='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'");
                                    foreach (DataRow row in arrRowsBom)
                                    {
                                        row["Weight"] = strWeight;
                                        row["Total_Weight"] = sap_weight_glass;
                                        row["Weight_Type"] = "BOM计重法-玻璃计重";
                                        //CBM和重量为0时插入错误日志表
                                        if (sap_weight_glass == 0)
                                            Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(dataTable1.Rows[0]["TRANSPORT_NO"].ToString(), dataTable1.Rows[0]["MO_NO"].ToString(), dataTable1.Rows[0]["MATERIAL_NO"].ToString(), dataTable1.Rows[0]["MATERIAL_DESC"].ToString(),
                   row["Material_No"].ToString(), row["Material_Desc"].ToString(), "", "", "", "玻璃计重为0");

                                    }
                                }
                            }
                            //外发计重
                            if (strMaterialNo.StartsWith("3"))
                            {
                                if (dt_CBM_WT_From_SAP.Select("ISNULL(PUCHASE_FLAG,'')='F' AND ISNULL(MES_DECOR,'')='' AND MATERIAL_NO='" + strMaterialNo + "'").Length > 0)
                                {
                                    list_3.Add(strMaterialNo);
                                    DataTable dt_from_sap_Temp = dt_CBM_WT_From_SAP.Select("ISNULL(PUCHASE_FLAG,'')='F' AND ISNULL(MES_DECOR,'')='' AND MATERIAL_NO='" + strMaterialNo + "'").CopyToDataTable();
                                    strWeight = dt_from_sap_Temp.Rows[0]["MATERIAL_WEIGHT"].ToString();
                                    if (order_qty > 0)
                                        sap_weight_f = Convert.ToDecimal(strWeight) * Carton_Qty / order_qty;

                                    //计算单个BOM物料的单重和总重，返回给前台
                                    DataRow[] arrRowsBom = dt_BOM.Select("MATERIAL_NO='" + strMaterialNo + "' and " +
                                        "Package_Mo_Main='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'");
                                    foreach (DataRow row in arrRowsBom)
                                    {
                                        row["Weight"] = strWeight;
                                        row["Total_Weight"] = sap_weight_f;
                                        row["Weight_Type"] = "BOM计重法-外发计重";
                                        //CBM和重量为0时插入错误日志表
                                        if (sap_weight_f == 0)
                                            Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(dataTable1.Rows[0]["TRANSPORT_NO"].ToString(), dataTable1.Rows[0]["MO_NO"].ToString(), dataTable1.Rows[0]["MATERIAL_NO"].ToString(), dataTable1.Rows[0]["MATERIAL_DESC"].ToString(),
                  row["Material_No"].ToString(), row["Material_Desc"].ToString(), "", "", "", "外发计重为0");

                                    }
                                }                            
                            }

                            sap_weight_sum = sap_weight_sum + sap_weight_glass + sap_weight_f;
                        
                            
                        }
                    }
                    //铁屏风计重
                    IEnumerable<string> exceptLs_TPF = exceptLs.ToList().Except(list_3);
                    if(exceptLs_TPF.ToList().Count>0)
                    {
                        foreach (var strTPF in exceptLs_TPF)
                        {
                            if (!strTPF.StartsWith("3"))
                                continue;
                            Dictionary<string, double> keyValues = GetTPF_Size(strTPF);
                            if (keyValues.Count > 0)
                            {
                                decimal weight_tpf = 0;//当个物料铁屏风重量
                                //需求数量
                                decimal Carton_Qty = dt_MO_Temp1.Select("Material_No='" + strTPF + "'").Length > 0 ?
                                   Convert.ToDecimal(dt_MO_Temp1.Select("Material_No='" + strTPF + "'").CopyToDataTable().Compute("sum(Demand_QTY)", ""))
                                    : 0;
                                double dLength = 0, dWidth = 0, dWeight = 0;
                                keyValues.TryGetValue("LENGTH", out dLength);
                                keyValues.TryGetValue("WIDTH", out dWidth);
                                keyValues.TryGetValue("WEIGHT", out dWeight);
                                if (order_qty > 0)
                                    weight_tpf = Convert.ToDecimal(dLength * dWidth * dWeight / 1000000) * (Carton_Qty / order_qty);
                                //计算单个BOM物料的单重和总重，返回给前台
                                DataRow[] arrRowsBom = dt_BOM.Select("MATERIAL_NO='" + strTPF + "' and Package_Mo_Main='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'");
                                foreach (DataRow row in arrRowsBom)
                                {
                                    row["Weight"] = dWeight;
                                    row["Total_Weight"] = weight_tpf;
                                    row["Weight_Type"] = "BOM计重法-铁屏风计重";
                                    //CBM和重量为0时插入错误日志表
                                    if (weight_tpf == 0)
                                        Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(dataTable1.Rows[0]["TRANSPORT_NO"].ToString(), dataTable1.Rows[0]["MO_NO"].ToString(), dataTable1.Rows[0]["MATERIAL_NO"].ToString(), dataTable1.Rows[0]["MATERIAL_DESC"].ToString(),
              row["Material_No"].ToString(), row["Material_Desc"].ToString(), dLength.ToString(), dWidth.ToString(), "", "铁屏风计重为0");

                                }
                                weight_tpf_sum = weight_tpf_sum + weight_tpf;
                            }
                        }
                    }
                    //其他重量-桌子|屏风
                    decimal cbm = dataTable1.Rows[0]["UNIT_VOLUME"].ToString().Length > 0 ? Convert.ToDecimal(dataTable1.Rows[0]["UNIT_VOLUME"].ToString()) : 0;
                    //桌子                  
                    if (dataTable1.Rows[0]["PRODUCT_LINE"].ToString()== "Laminate")
                        other_weight = cbm * 4;                   
                    //屏风
                    else
                        other_weight = cbm * 6;

                    //BOM累计总重
                    bom_weight = board_weight_sum + pl_weight_sum + sap_weight_sum + other_weight + weight_tpf_sum;

                    //回写临时表
                    if (bom_weight > 0)
                    {
                        DataRow[] arrRows = table_DN_Temp.Select("MO_NO='" + dt_Mo_Main.Rows[k]["MO_NO"].ToString() + "'");
                        foreach (DataRow row in arrRows)
                        {
                            row["UNIT_WEIGHT"] = bom_weight;
                            row["CAL_TYPE"] = "BOM计重" + " " + row["CAL_TYPE"];
                            //CBM和重量为0时插入错误日志表
                            if (bom_weight == 0)
                                Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(row["TRANSPORT_NO"].ToString(), row["MO_NO"].ToString(), row["MATERIAL_NO"].ToString(), row["MATERIAL_DESC"].ToString(),
                     "", "", "", "", "", "BOM计重为0");

                        }
                    }
                }
            }
            dt_Temp = table;//回写到前台
            return table_DN_Temp;
        }

        //获取【WH_Mat_Unit_Weight_Per_Square】表数据
        public static DataTable Get_Weight_Per_Square()
        {
            DataTable dataTable = new DataTable();
            string sql = @"SELECT [MAT_TYPE]
      ,[MAT_THICK]
      ,[WEIGHT_PER_M2]
  FROM [LamexMESManager].[dbo].[WH_Mat_Unit_Weight_Per_Square]";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }

        //计算[临时DN表]出货CBM及重量
        public static DataTable Get_Table_CBM_Weight(DataTable table_DN_Temp)
        {
            for (int i = 0; i < table_DN_Temp.Rows.Count; i++)
            {
                decimal SHIPPING_VOLUME = 0;//出货体积
                decimal SHIPPING_WEIGHT = 0;//出货重量
                decimal SHIPPING_QTY = table_DN_Temp.Rows[i]["SHIPPING_QTY"].ToString().Length > 0 ? Convert.ToDecimal(table_DN_Temp.Rows[i]["SHIPPING_QTY"].ToString()) : 0;
                decimal UNIT_VOLUME = table_DN_Temp.Rows[i]["UNIT_VOLUME"].ToString().Length > 0 ? Convert.ToDecimal(table_DN_Temp.Rows[i]["UNIT_VOLUME"].ToString()) : 0;
                decimal QRDER_QTY= table_DN_Temp.Rows[i]["ORDER_QTY"].ToString().Length > 0 ? Convert.ToDecimal(table_DN_Temp.Rows[i]["ORDER_QTY"].ToString()) : 0;
                decimal UNIT_WEIGHT = table_DN_Temp.Rows[i]["UNIT_WEIGHT"].ToString().Length > 0 ? Convert.ToDecimal(table_DN_Temp.Rows[i]["UNIT_WEIGHT"].ToString()) : 0;
                //CBM
                if (table_DN_Temp.Rows[i]["CARTON_QTY"].ToString().Length > 0)
                {
                    decimal CARTON_QTY = Convert.ToDecimal(table_DN_Temp.Rows[i]["CARTON_QTY"].ToString());
                    //原始需求： SHIPPING_VOLUME = Math.Round(CARTON_QTY * SHIPPING_QTY, 1) * UNIT_VOLUME;
                    //2020.10.20修改： CBM=(最大尺寸纸箱数量/订单数量*出货数量)*单位CBM 。(最大尺寸纸箱数量/订单数量*出货数量)=不足1时按1计算
                    //2020.10.30修改：增加：需求数量/订单数量>1时，按1取值
                    if (QRDER_QTY > 0)
                        SHIPPING_VOLUME = CARTON_QTY / QRDER_QTY * SHIPPING_QTY < 1
                            ? UNIT_VOLUME
                            : (CARTON_QTY / QRDER_QTY > 1
                                ? SHIPPING_QTY * UNIT_VOLUME
                                : Math.Round(CARTON_QTY / QRDER_QTY * SHIPPING_QTY, 1) * UNIT_VOLUME);
                }
                else
                {
                    SHIPPING_VOLUME = SHIPPING_QTY * UNIT_VOLUME;
                }
                //重量
                SHIPPING_WEIGHT = SHIPPING_QTY * UNIT_WEIGHT;
                //回写到DataTable
                DataRow[] arrRows = table_DN_Temp.Select("TRANSPORT_NO='" + table_DN_Temp.Rows[i]["TRANSPORT_NO"].ToString() + "' " +
                    "and ORDER_NO='" + table_DN_Temp.Rows[i]["ORDER_NO"].ToString() + "' " +
                    "and LINE_NO='" + table_DN_Temp.Rows[i]["LINE_NO"].ToString() + "'");
                foreach (DataRow row in arrRows)
                {
                    row["SHIPPING_WEIGHT"] = SHIPPING_WEIGHT;
                    row["SHIPPING_VOLUME"] = SHIPPING_VOLUME;
                }
            }
            return table_DN_Temp;
        }

        //查询铁屏风尺寸-返回基重|尺寸
        public static Dictionary<string, double> GetTPF_Size(string strPartNo)
        {
            Dictionary<string, double> dict = new Dictionary<string, double>();
            //根据物料号查询尺寸
            string sql = @"SELECT [PART_NO]
      ,[DWG_NO]
      ,[TYPE_CODE]
      ,[LENGTH]
      ,[WIDTH]
      ,[THICK]
      FROM [LamexMESManager].[dbo].[Transfer_TPF_Size] WHERE PART_NO='" + strPartNo + "'";
            DataTable dataTable = BaseBuildSql.GetDataTableBySql(sql);
            //根据Decor和厚度查询基重
            if (dataTable.Rows.Count > 0)
            {
                string strLength = dataTable.Rows[0]["LENGTH"].ToString();
                string strWidth = dataTable.Rows[0]["WIDTH"].ToString();             
                string strThick = dataTable.Rows[0]["THICK"].ToString();
                string strTYPE_CODE = dataTable.Rows[0]["TYPE_CODE"].ToString();             
                if (!string.IsNullOrEmpty(strThick) && !string.IsNullOrEmpty(strTYPE_CODE))
                {
                    string sqlWeight = @"SELECT [WEIGHT] FROM [LamexMESManager].[dbo].[WH_CBM_Base_Weight]
     WHERE TYPE_CODE='" + strTYPE_CODE + "' AND THICK=" + strThick + "";
                    DataTable dtWeight = BaseBuildSql.GetDataTableBySql(sqlWeight);
                    if (dtWeight.Rows.Count > 0)
                    {
                        string strWeight = dtWeight.Rows[0]["WEIGHT"].ToString();
                        dict.Add("LENGTH", strLength.Length > 0 ? Convert.ToDouble(strLength) : 0);
                        dict.Add("WIDTH", strWidth.Length > 0 ? Convert.ToDouble(strWidth) : 0);
                        dict.Add("WEIGHT", strWeight.Length > 0 ? Convert.ToDouble(strWeight) : 0);
                    }

                }
            }
            return dict;
        }

        //钢脚重量和CBM计算
        public static DataTable Get_Table_MetalParts(DataTable table_DN_Temp, DataTable dataTable , List<CartonMaterial> cartons, DataTable table_MateralParts,DataTable table_Decor)
        {
                
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {

                decimal dmlUNIT_WEIGHT_Toatl = 0;
                decimal dmlUNIT_VOLUME_Total = 0;
                string strMO_NO = dataTable.Rows[i]["MO_NO"].ToString();
                string strTRANSPORT_NO = dataTable.Rows[i]["TRANSPORT_NO"].ToString();
                DataRow[] arrRows = table_DN_Temp.Select("TRANSPORT_NO='" + strTRANSPORT_NO + "' and MO_NO='" + strMO_NO + "'");
                #region 计算体积            
                //按纸箱算法计算体积 
                //bool IsCarton = false;
                foreach (var item in cartons)
                {
                    //存在纸箱
                    if (item.MO_NO == strMO_NO)
                    {
                        foreach (DataRow row in arrRows)
                        {
                            row["CARTON_QTY"] = item.MaterialQTY;
                            dmlUNIT_VOLUME_Total = item.CartonCBM;
                            row["VOLUME_LENGTH"] = item.VOLUME_LENGTH;
                            row["VOLUME_WIDTH"] = item.VOLUME_WIDTH;
                            row["VOLUME_HEIGHT"] = item.VOLUME_HEIGHT;
                        }
                        //IsCarton = true;
                        break;
                    }
                }
                ////不存在纸箱
                //if (!IsCarton)
                //{
                //    //foreach (DataRow row in arrRows)
                //    //{
                //    //    //row["UNIT_VOLUME"] = 0;
                //    //    //row["VOLUME_LENGTH"] = 0;
                //    //    //row["VOLUME_WIDTH"] = 0;
                //    //    //row["VOLUME_HEIGHT"] = 0;
                //    //}
                //}



                #endregion
                #region 计算重量
                //获取符合条件的生产订单表
                DataTable table_Order = new DataTable();
                DataRow[] dr = table_MateralParts.Select("Package_Mo_Main='"+ strMO_NO + "'");
                if (dr.Length > 0)
                {
                    table_Order = dr.CopyToDataTable();
                    for (int k = 0; k < table_Order.Rows.Count; k++)
                    {
                        decimal strSize = Get_MP_CuttingSize(table_Order.Rows[k]["Part_No"].ToString()).Length > 0 ?
                            Convert.ToDecimal(Get_MP_CuttingSize(table_Order.Rows[k]["Part_No"].ToString())) : 0;//开料尺寸
                       
                        string strDecor = table_Order.Rows[k]["MES_DECOR"].ToString();//Decor
                        decimal Qty = table_Order.Rows[k]["Part_QTY"].ToString().Length > 0 ?
                            Convert.ToDecimal(table_Order.Rows[k]["Part_QTY"].ToString()) : 0; //数量
                        decimal Weight_PER_METER = 0;//每米重量
                        decimal Weight_Single = 0;//单个重量        

                        //根据Decor查询重量
                        DataRow[] drDecor = table_Decor.Select("DECOR='" + strDecor + "'");
                        if (drDecor.Length > 0)
                            Weight_PER_METER = Convert.ToDecimal(drDecor.CopyToDataTable().Rows[0]["WEIGHT_PER_METER"].ToString());
                        if (strSize > 0)
                            Weight_Single = strSize / 1000 * Weight_PER_METER * Qty;
                        dmlUNIT_WEIGHT_Toatl = dmlUNIT_WEIGHT_Toatl + Weight_Single;
                    }
                }
                #endregion
                //5 计算为0的写到错误日志表
                if (dmlUNIT_WEIGHT_Toatl == 0 || dmlUNIT_VOLUME_Total == 0)
                    Common.CRUDHelper.Insert_SA_CBM_WT_ErrorLog(dataTable.Rows[i]["TRANSPORT_NO"].ToString(), dataTable.Rows[i]["MO_NO"].ToString(),
                         dataTable.Rows[i]["MATERIAL_NO"].ToString(), dataTable.Rows[i]["MATERIAL_DESC"].ToString(),
                   "", "", "", "", "", "钢脚取数为0");

                //6 把结果更新到DN表
                foreach (DataRow row in arrRows)
                {
                    row["UNIT_WEIGHT"] = dmlUNIT_WEIGHT_Toatl;
                    row["UNIT_VOLUME"] = dmlUNIT_VOLUME_Total;
                    row["CAL_TYPE"] = "钢脚取数";
                }
            }


            return table_DN_Temp;
        }
        ////查询Transfer_SA_DN_List表
        //public static DataTable GetTable_SA_DN_List(DataTable table_DN_Temp)
        //{
        //    DataTable dataTable = new DataTable();
        //    List<string> list = table_DN_Temp.AsEnumerable().Select(d => d.Field<string>("TRANSPORT_NO")).ToList();
        //    StringBuilder stringBuilder = new StringBuilder();
        //    stringBuilder.AppendFormat(@" AND TRANSPORT_NO  IN ('{0}')", string.Join("','", list.ToArray()));

        //    string sql = @"SELECT [TRANSPORT_NO]
        //                         ,[MRP_CONTROLLER]
        //                         ,MO_NO
        // FROM [LamexMESManager].[dbo].[Transfer_SA_DN_List] where 1=1 " + stringBuilder.ToString() + "";
        //    dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
        //    return dataTable;
        //}
        //查询钢脚订单信息
        public static DataTable GetMertalParts(DataTable table_DN_Temp)
        {
            DataTable dataTable = new DataTable();
            List<string> list = table_DN_Temp.AsEnumerable().Select(d => d.Field<string>("MO_NO")).ToList();
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendFormat(@" AND Package_Mo_Main  IN ('{0}')", string.Join("','", list.ToArray()));

            string sql = @"SELECT [Package_Mo_Main]
      ,[Package_Mo]
      ,[Part_No]
      ,[Drawing_No]
      ,[Part_QTY]
      ,[Order_Number]
      ,[Part_Desc]
      ,[MRP_Owner]
      ,[Prod_Category]
      ,[MES_DECOR]
  FROM [LamexMES_MetalParts].[dbo].[MP_Transfer_Part_Detailed] where 1=1 " + stringBuilder.ToString() + "";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        //查询钢脚Decor对应的重量信息
        public static DataTable GetMertalParts_Decor()
        {
            DataTable dataTable = new DataTable();         
            string sql = @"SELECT [DECOR]
      ,[WEIGHT_PER_METER]
  FROM [LamexMESManager].[dbo].[WH_Mat_DECOR]";
            dataTable = Lamex.Base.BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        //查询钢脚开料尺寸
        public static string Get_MP_CuttingSize(string strPart_No)
        {
            string strCuttingSize = "";
            string sql = @"SELECT TOP 1 [Part_No]
      ,[Dwg_No]
      ,[Part_Type]
      ,[Cutting_Size]
      ,[Decor]
      ,[Product_Dwg_No]
      ,[Effective_Date]
      ,[End_Date]
  FROM [LamexMES_MetalParts].[dbo].[MP_Cutting_Size]
  WHERE Part_No='" + strPart_No + "' ORDER BY Effective_Date DESC";
            DataTable dataTable = BaseBuildSql.GetDataTableBySql(sql);
            if (dataTable.Rows.Count > 0)
                strCuttingSize = dataTable.Rows[0]["Cutting_Size"].ToString();
            return strCuttingSize;
        }

    }
}
